package etl;
import java.io.EOFException;
import java.sql.*;
import java.util.Calendar;

public class Extractor {
	
	/**
	 * This array works as follows:
	 * The first element of every sub-array is our internal name for the location type.
	 * The rest of the elements are strings matched against the Lyngsoe location codes.
	 * Example: If a location contains "ebs", it will be marked as "storage" in our system.
	 */
	private String[][] KNOWN_READER_TYPES = {
			{"sorter", "sorter"},
			{"check-in", "check-in"},
			{"arrival", "arrival"},
			{"gateway", "gateway"},
			{"transfer", "transfer"},
			{"storage", "ebs"},
	};
	
	private Connection sqlCon;
	private int lastReadingId = 0;
	
	public void connect() throws SQLException {
		
		try {
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		sqlCon = DriverManager.getConnection("jdbc:mysql://127.0.0.1/lyngsoe", "root", "derpderpderp");
		
	}
	
	public void disconnect() throws SQLException {
		
		sqlCon.close();
		
	}
	
	public void setLastReadingId(int id)
	{
		lastReadingId = id;
	}
	
	public ReadingData extractNextRow() throws SQLException, EOFException {
		
		ReadingData parsedRow = new ReadingData();
		
		Statement s = sqlCon.createStatement();
		
		// Query the Lyngsoe database for a reading row
		ResultSet res = s.executeQuery(
				"SELECT " +
				
				"r.`READING_ID`, " +
				"r.`LICENSE_PLATE`, " +
				"r.`READING_TS`, " +
				"r.`LOCATION_ID`, " +
				"r.`ORIGINATING_AIRPORT`, " +

				"l.`LOCATION_NAME`, " +
				"l.`SITE_ID`, " +
				
				"rl1.`DESTINATION`, " +
				"rl1.`AIRLINE`, " +
				"rl2.`DESTINATION`, " +
				"rl2.`AIRLINE`, " +
				"rl3.`DESTINATION`, " +
				"rl3.`AIRLINE`, " +
				"rl4.`DESTINATION`, " +
				"rl4.`AIRLINE`, " +
				"rl5.`DESTINATION`, " +
				"rl5.`AIRLINE`, " +
				"rl6.`DESTINATION`, " +
				"rl6.`AIRLINE` " +
				
				"FROM `reading` AS r " +
				
				"NATURAL LEFT JOIN `location` AS l " +
				
				"LEFT JOIN `route_leg` AS rl1 " +
				"ON r.`ROUTE_LEG1` = rl1.`ROUTE_LEG_ID` " +
				"LEFT JOIN `route_leg` AS rl2 " +
				"ON r.`ROUTE_LEG2` = rl2.`ROUTE_LEG_ID` " +
				"LEFT JOIN `route_leg` AS rl3 " +
				"ON r.`ROUTE_LEG3` = rl3.`ROUTE_LEG_ID` " +
				"LEFT JOIN `route_leg` AS rl4 " +
				"ON r.`ROUTE_LEG4` = rl4.`ROUTE_LEG_ID` " +
				"LEFT JOIN `route_leg` AS rl5 " +
				"ON r.`ROUTE_LEG5` = rl5.`ROUTE_LEG_ID` " +
				"LEFT JOIN `route_leg` AS rl6 " +
				"ON r.`ROUTE_LEG6` = rl6.`ROUTE_LEG_ID` " +
				
				"WHERE r.`READING_ID` > " + lastReadingId + " " +
				"LIMIT 1");
		
		// If the query returned 0 rows, we have reached the end of the database.
		// We signal this by returning null.
		if (!res.next())
		{
			s.close();
			res.close();
			
			throw new EOFException("No more readings in database.");
			
		}
		
		// If the site ID is 0, the reading is broken.
		if (res.getInt("l.SITE_ID") == 0 || res.getString("r.ORIGINATING_AIRPORT").toLowerCase().equals("nul"))
		{
			lastReadingId = res.getInt("r.READING_ID");
			
			//System.out.println("Reading #" + lastReadingId + " is broken. Skipping.");
			s.close();
			res.close();
			
			return null;
		}
		
		// Parse and enter the row into our new ReadingData object.
		parsedRow.licensePlate = Long.parseLong(res.getString("r.LICENSE_PLATE"));
		parsedRow.originatingAirport = res.getString("r.ORIGINATING_AIRPORT").toLowerCase();
		for (int i=1; i<=6; i++)
		{
			String dest = res.getString("rl"+i+".DESTINATION");
			String air = res.getString("rl"+i+".AIRLINE");
			
			if (dest == null)
			{
				parsedRow.numRouteLegs = i-1;
				break;
			}
			
			parsedRow.routeLegs[i-1] = new ReadingData.Leg(dest.toLowerCase(),air.toLowerCase());
		}
		parsedRow.timestamp = res.getTimestamp("r.READING_TS", Calendar.getInstance());
		parsedRow.locationId = res.getInt("r.LOCATION_ID");
		parsedRow.readerLocation = res.getString("l.LOCATION_NAME");
		
		// Parse the reader location
		String[] locationParts = parsedRow.readerLocation.split("\\.",3);
		try {
			parsedRow.countryCode = locationParts[0];
			parsedRow.airportCode = locationParts[1];
			parsedRow.readerLocation = locationParts[2];
			
			boolean done = false;
			for (String[] type : KNOWN_READER_TYPES)
			{
				String name = type[0];
				for (int i=1; i<type.length; i++)
				{
					if (parsedRow.readerLocation.contains(type[i]))
					{
						parsedRow.readerLocationType = name;
						done = true;
						break;
					}
				}
				if (done) break;
			}
			if (parsedRow.readerLocationType == null) parsedRow.readerLocationType = "unknown";
			
		} catch (ArrayIndexOutOfBoundsException e) {
			
			if (parsedRow.countryCode == null) parsedRow.countryCode = "Unknown";
			if (parsedRow.airportCode == null) parsedRow.airportCode = "Unknown";
			if (parsedRow.readerLocation == null) parsedRow.readerLocation = "Unknown";
			if (parsedRow.readerLocationType== null) parsedRow.readerLocationType = "Unknown";
			
		}
		
		// Update lastReadingId so we know what row we have reached.
		lastReadingId = res.getInt("r.READING_ID");
		
		System.out.println("Extracted reading #" + lastReadingId);
		
		s.close();
		res.close();
		
		return parsedRow;
		
	}
	
}
